

/**
 * 封装所有的sql语句
 */
module.exports = {
  users: {
    login: "select * from  users where  uname=? and  upass=?;",
    userByname: "select * from users where uname=?;",
    addUser: "insert into users values(null,?,?,null,?,'active','user');",

    getUserImg:`select * from users where uid=?;`,
    updateUpass: "update users set upass=? where uname=?;",
    uploadAvatar: `UPDATE users SET  avatar_img=? WHERE uid=?;`,


    checkCollection: `SELECT * FROM news_collect  WHERE news_id = ? AND uid = ?;`,
    addCollection: `INSERT INTO news_collect  VALUES (null,?,?,?);`,
    getNewsCollect:`SELECT n.news_id, n.news_title,n.news_img, n.digest,n.source, nc.collect_time, d.full_content
    FROM  news n  JOIN   news_detail d ON n.news_id = d.news_id  LEFT JOIN   
     news_collect nc ON n.news_id = nc.news_id where  nc.uid = ?;`,
    uCollectTotal: "SELECT COUNT(*) as news_total FROM news_collect WHERE uid = ?;",
    cancelCollection: `DELETE FROM news_collect WHERE news_id = ? AND uid = ?;`,



    checkThumbsUp: 'SELECT * FROM news_thumbsup WHERE news_id = ? AND uid = ?;',
    addThumbsUp: `INSERT INTO news_thumbsup VALUES (null,?,?);`,
    cancelThumbsUp: `delete from news_thumbsup where news_id=? and uid=?;`,
    checkeCommThumsbUp: `SELECT * FROM comments_thumbsup  WHERE comments_id=? AND uid = ?;`,
    addCommThumbsUp: `	INSERT INTO comments_thumbsup  VALUES (null,?,?);`,
    cancelCommThumbsUp: `delete from comments_thumbsup where comments_id=? and  uid=?;`,

    addNewsHistory: "INSERT INTO history  VALUES (null,?,?,?);",
    getNewsHistory: `SELECT h.news_id,n.news_title,n.news_img,n.digest,n.source FROM news n join history h on n.news_id=h.news_id where h.uid=2  ORDER BY h.history_id DESC;`

  },


  news: {
    
    getHomeNews: `SELECT  * from  news;`,
    // 获取首页轮播图数据
    getSwiperNews: "SELECT * FROM news  ORDER BY RAND() LIMIT 5;",

    getTypeNewHome: `SELECT  n.news_id,n.news_title,n.news_img,n.digest,n.source,
    n.post_date FROM   news n  JOIN  categories c ON n.category_id = c.category_id  where  c.category_id=?;`,


    getNewsdetail: `SELECT   n.news_id,n.news_title,n.news_img,n.source,n.post_date,d.full_content,
    c.category_name  FROM  news n JOIN   news_detail d ON n.news_id = d.news_id  JOIN   categories
     c ON n.category_id = c.category_id  where   n.news_id=?;`,
    //  getNewsdetailUser: `SELECT   n.news_id,n.news_title,n.news_img,n.source,n.post_date,d.full_content,
    //  c.category_name  FROM  news n JOIN   news_detail d ON n.news_id = d.news_id  JOIN   categories
    //   c ON n.category_id = c.category_id  where   n.news_id=?;`,
    

    searchNews: "SELECT * from news where  news_title like ? or source like ?;",

    newsThumbsupTotal: `SELECT n.*, COUNT(t.thumbsup_id) AS thumbsup_total FROM news n  
       LEFT JOIN news_thumbsup t  ON n.news_id = t.news_id  WHERE n.news_id = ? GROUP BY n.news_id;`,
    newsCollectdTotal: `SELECT n.*, COUNT(c.collect_id) AS collect_total FROM news n  
    LEFT JOIN news_collect c  ON n.news_id = c.news_id   WHERE n.news_id=? GROUP BY n.news_id;`,
    renderAddNews:"SELECT * FROM news n join news_detail d on n.news_id=d.news_id  ORDER BY n.post_date DESC  LIMIT 100;"
  },


  comments: {
    getComments: `	SELECT    c.comments_id,  c.comments_content,  u.uname,   u.avatar_img,   c.post_time  
    FROM   comments c  JOIN   users u ON c.uid = u.uid  WHERE    c.news_id =?;`,
    addComments: `INSERT  INTO  comments  VALUES(null,?,?,?,0,?);`,
    getselfComments: `SELECT   c.comments_id,  c.comments_content,  u.uname,  u.avatar_img,  c.post_time  
    FROM   comments c  JOIN   users u ON c.uid = u.uid  WHERE   c.news_id = ? AND  c.uid = ?;`,
    deleteComments:`DELETE FROM comments  WHERE comments_id=? and uid=?;`,

  },


  admin: {

  },

  userManger: {
    getUsers: "select * from users  where role='user';",
    recoverAccount: "UPDATE users SET status ='active' WHERE uid =? and role='user';",
    freezeAccount: "UPDATE users SET status = 'freeze' WHERE uid =? and role='user';",
   

  },


  newsManger: {
    deleteNews: `DELETE  FROM news WHERE news_id=?;`,
    postNews:`INSERT INTO news  VALUES(?,?,?,?,?,?,?,?);`,
    postNewsDetail:`insert INTO news_detail VALUES(null,?,?);`
  },

  commManger: {
    deleteUserComments: `DELETE FROM comments WHERE comments_id = ?;`,
    getNesComments:`	 SELECT  c.comments_id,n.news_id, n.news_title, u.uname,c.comments_content FROM   
     news n  JOIN   comments c ON n.news_id = c.news_id  JOIN   users u ON c.uid = u.uid ORDER BY    n.news_id, c.comments_id;`,
  }

}